<?php
// +----------------------------------------------------------------------
// | zlserver [ 小程序 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2017~2018 http://www.zhongliansoft.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: tony <tanyu@zhongliansoft.com>
// +----------------------------------------------------------------------
namespace app\job\import;

use think\exception\ErrorException;
use think\facade\Validate;
use think\queue\Job;
use app\common\model\TeacherInfo as TeacherInfoModel; 
use app\common\model\User as UserModel; 
use app\common\model\Dept as DeptModel;
use app\common\model\ViewGetDepts as ViewGetDeptsModel; 
use app\common\model\ViewGetSpecicalties as ViewGetSpecicaltiesModel; 
use app\common\model\CwStructCache as CwStructCacheModel;
use think\Db;


use app\common\model\Ietask;
use think\facade\Log;

class Teacher
{
    protected $rule = [
        'bm' => 'require',
        'zy' => 'require',
        'bj' => 'require',
        'truename' => 'require',
        'mobile' => 'require', 
    ];
    protected $msg = [
        'bm.require' => '部院必填',
        'zy.require' => '专业必填',
        'bj.require' => '班级必填',
        'truename.require' => '辅导员姓名必填',
        'mobile.require' => '辅导员手机必填',
    ];

    // 数据校验
    public function check($fileId, $filePath)
    {
        $re = [
            'count' => 0,
            'code' => 0,
            'msg' => ''
        ];

        $viewGetDeptsModel = new ViewGetDeptsModel();
        $viewGetSpecicaltiesModel = new ViewGetSpecicaltiesModel();
        $cwStructCacheModel = new CwStructCacheModel();
        $userModel = new UserModel();
        $teacherInfoModel = new TeacherInfoModel();
        $DeptModel = new DeptModel();

        $data = [];
        $errorData = [];
        try {
            $resXls=import_excel($filePath);  

            if ($resXls['status']) {
                // 清理导入提示
                unset($resXls['data'][0]);
                // 取表头
                $header = $resXls['data'][1];
                unset($resXls['data'][1]);
                $title = $teacherInfoModel->csvHeader();
                $fields = [];

                // 遍历表头
                $last_index = 0;
                foreach ($title as $key => $val) {
                    // 从导入的数据表中查找字段位置
                    $index = array_search($val['desc'], $header);
                    if ($index !== false) {
                        $fields[] = [
                            'index' => $index,
                            'value' => $val['id']
                        ];
                    }
                    $last_index = $index;
                }
                
                if(count($fields) != 5) { 
                    $re['msg']  = '文件格式错误！请先下载导入模板，按模板要求准备导入数据后再次导入！';
                    $re['code']  = -1;
                    return $re;
                } else {

                    // 数据填充
                    $iData = [];
                    if ($fields) {
                        $i = 0;
                        foreach ($resXls['data'] as $key => $val) {
                            foreach ($fields as $fkey => $fval) {
                                $iData[$i][$fval['value']] = $val[$fval['index']];
                                if(is_object($iData[$i][$fval['value']])) {
                                    $iData[$i][$fval['value']] = $iData[$i][$fval['value']]->__toString();
                                }
                            }
                            $i++;
                        }
                    }
    
                    // 增加描述字段
                    $fields[] = [
                        'index' => $last_index + 1,
                        'value' => 'desc'
                    ];
                    
                    $count = count($iData);
                    $errCount = 0;
     
                    // 部院
                    $deptList = $viewGetDeptsModel->getNameList();
                    // 专业
                    $specialtyList = $viewGetSpecicaltiesModel->getNameList(); 
                    // 班级
                    $classList = $cwStructCacheModel->getClassesList();
                    // 班级对应部院
                    $classDeptList = $cwStructCacheModel->getClassDeptList(); 
                    // 班级对应专业
                    $classSpecList = $cwStructCacheModel->getClassSpecList(); 
                    // 手机号对应辅导员
                    $mobileTeacherList = $userModel->getmobileNameList();

                    //获取部门管理名称列表
                    $deptNameList = $DeptModel->getDeptList();
                    
                    $tmp_arr = array(); 
                    // 数据预处理 
                    foreach ($iData as $key => $val) {
                        $errFind = false;
                        //校验数据
                        $validate = new \think\Validate($this->rule, $this->msg);
                        $validate->batch(true);

                        // 数据预处理
                        $teacher['bm'] = trim($val['bm']);
                        $teacher['zy'] = trim($val['zy']);
                        $teacher['bj'] = trim($val['bj']);
                        $teacher['truename'] = trim($val['truename']);
                        $teacher['mobile'] = trim($val['mobile']);
                        $teacher['desc'] = '';
                        $teacher['error'] = 0;
 
                        if (!$validate->check($teacher)) {
                            $teacher['error'] = 1;
                            $errCount = $errCount + 1;
                            $errFind = true;

                            $index = 0;
                            foreach ($validate->getError() as $errKey => $value) {
                                if($index == 0) {
                                    $teacher['desc']  = $teacher['desc'] . $value;
                                } else {
                                    $teacher['desc']  = $teacher['desc'] . '；' . $value;
                                }
                                $index++;
                            }  
                        }

                        // 自身检测重复数据
                        if (in_array($teacher['bm'].$teacher['zy'].$teacher['bj'], $tmp_arr)) { //搜索$v[$key]是否在$tmp_arr数组中存在，若存在返回true
                            $teacher['error'] = 1;
                            if($errFind) {
                                $teacher['desc'] = $teacher['desc'] . '；' . '导入数据中【同部门同专业同班级】重复'; 
                            } else {
                                $errCount = $errCount + 1;
                                $errFind = true;
                                $teacher['desc'] = $teacher['desc'] . '导入数据中【同部门同专业同班级】重复'; 
                            }
                        } else {
                            if($teacher['bm'].$teacher['zy'].$teacher['bj'] != '') {
                                $tmp_arr[] = $teacher['bm'].$teacher['zy'].$teacher['bj'];
                            }
                        }

                        // 判断部院是否存在
                        if (!in_array($teacher['bm'], $deptList)) {
                            $teacher['error'] = 1;                
                            if($errFind) {
                                $teacher['desc'] =  $teacher['desc'] . '；' . $teacher['bm'] . '部院不存在';
                            } else {
                                $errCount = $errCount + 1;
                                $errFind = true;
                                $teacher['desc'] =  $teacher['bm'] . '部院不存在';
                            }
                        }

                        // 判断部院在部门管理中是否存在
                        if (!in_array($teacher['bm'], $deptNameList)) {
                            $teacher['error'] = 1;
                            if($errFind) {
                                $teacher['desc'] =  $teacher['desc'] . ';' . $teacher['bm'] . '在部门管理中不存在，请联系管理员增加';
                            } else {
                                $errCount = $errCount + 1;
                                $errFind = true;
                                $teacher['desc'] =  $teacher['bm'] . '在部门管理中不存在，请联系管理员增加';
                            }
                        }

                        // 判断专业是否存在
                        if (!in_array($teacher['zy'], $specialtyList)) {
                            $teacher['error'] = 1;
                            if($errFind) {
                                $teacher['desc'] =  $teacher['desc'] . '；' . $teacher['zy'] . '专业不存在';
                            } else {
                                $errCount = $errCount + 1;
                                $errFind = true;
                                $teacher['desc'] =  $teacher['zy'] . '专业不存在';
                            }
                        }

                        // 判断班级是否存在
                        if (!in_array($teacher['bj'], $classList)) {
                            $teacher['error'] = 1;
                            if($errFind) {
                                $teacher['desc'] =  $teacher['desc'] . '；' . $teacher['bj'] . '班级不存在';
                            } else {
                                $errCount = $errCount + 1;
                                $errFind = true;
                                $teacher['desc'] =  $teacher['bj'] . '班级不存在';
                            }
                        } else{                        
                            // 判断班级和部院是否相符
                            // if($teacher['bm'] != $classDeptList[$teacher['bj']]){
                            if(!$this->bjbmkeyvalisinlist($teacher['bj'], $teacher['bm'], $classDeptList)){
                                $teacher['error'] = 1;
                                if($errFind) {
                                    $teacher['desc'] =  $teacher['desc'] . '；' . '班级与部/院不符';
                                } else {
                                    $errCount = $errCount + 1;
                                    $errFind = true;
                                    $teacher['desc'] =  $teacher['desc'] . '班级与部/院不符';
                                }
                            } else {
                                // 判断班级和专业是否相符
								if(!$this->bjzykeyvalisinlist($teacher['bj'], $teacher['zy'], $classSpecList)){
                                // if($teacher['zy'] != $classSpecList[$teacher['bj']]){
                                    $teacher['error'] = 1;
                                    if($errFind) {
                                        $teacher['desc'] =  $teacher['desc'] . '；' . '班级与专业不符';
                                    } else {
                                        $errCount = $errCount + 1;
                                        $errFind = true;
                                        $teacher['desc'] =  $teacher['desc'] . '班级与专业不符';
                                    }
                                } else {
                                    // 判断班级是否存在学生
                                    $bj = $teacher['bj'];
                                    $zy = $teacher['zy'];
                                    $bm = $teacher['bm'];
    
                                    $sql = "SELECT count(a.xh) as count
                                    FROM zlcw.zl_student_info a
                                        JOIN zlzhsf.pxsdm c
                                        JOIN zlzhsf.pbjdm d
                                        JOIN zlzhsf.pzydm e
                                        JOIN zlzhsf.pbmdm f
                                    WHERE a.xh = c.XH AND c.BJDM = d.BJDM AND c.ZYDM = e.ZYDM AND c.BMDM = f.BMDM
                                        AND d.BJMC = '".$bj."'
                                        AND e.ZYMC = '".$zy."'
                                        AND f.BMMC = '".$bm."'";
                                    $res = Db::query($sql);
                                    if(!$res[0]["count"]){
                                        $teacher['error'] = 1;
                                        if($errFind) {
                                            $teacher['desc'] =  $teacher['desc'] . '；' . '班级没有学生';
                                        } else {
                                            $errCount = $errCount + 1;
                                            $errFind = true;
                                            $teacher['desc'] =  $teacher['desc'] . '班级没有学生';
                                        }                               
                                    } else {
                                        // 已生成计划的需要先删除计划
                                        $sql = "SELECT count(a.xh) as count
                                        FROM zlcw.zl_student_info a
                                            JOIN zlzhsf.pxsdm c
                                            JOIN zlzhsf.pbjdm d
                                            JOIN zlzhsf.pzydm e
                                            JOIN zlzhsf.pbmdm f
                                            JOIN zlcw.zl_book_apply_detail w
                                            JOIN zlcw.zl_book_apply u
                                        WHERE a.xh = c.XH AND c.BJDM = d.BJDM AND c.ZYDM = e.ZYDM AND c.BMDM = f.BMDM
                                            AND a.xh = w.xh AND w.applyid = u.id
                                            AND d.BJMC = '".$bj."'
                                            AND e.ZYMC = '".$zy."'
                                            AND f.BMMC = '".$bm."'
                                            AND u.year = (select value from zlcw.zl_config where name = 'year')
                                            AND u.season = (select value from zlcw.zl_config where name = 'season')
                                            AND u.is_ff = 0";
                                        $res = Db::query($sql);
                                        if($res[0]["count"]){
                                            $teacher['error'] = 1;
                                            if($errFind) {
                                                $teacher['desc'] =  $teacher['desc'] . '；【'.$bm.'_'.$zy.'_'.$bj.'】已存在发放申请';
                                            } else {
                                                $errCount = $errCount + 1;
                                                $errFind = true;
                                                $teacher['desc'] = '【'.$bm.'_'.$zy.'_'.$bj.'】已存在发放申请';
                                            }
                                        } else {
                                            // 已签收的不能替换
                                            $sql = "SELECT count(a.xh) as count
                                            FROM zlcw.zl_student_info a
                                                JOIN zlzhsf.pxsdm c
                                                JOIN zlzhsf.pbjdm d
                                                JOIN zlzhsf.pzydm e
                                                JOIN zlzhsf.pbmdm f
                                                JOIN zlcw.zl_book_apply_detail w
                                                JOIN zlcw.zl_book_apply u
                                            WHERE a.xh = c.XH AND c.BJDM = d.BJDM AND c.ZYDM = e.ZYDM AND c.BMDM = f.BMDM
                                                AND a.xh = w.xh AND w.applyid = u.id
                                                AND d.BJMC = '".$bj."'
                                                AND e.ZYMC = '".$zy."'
                                                AND f.BMMC = '".$bm."'
                                                AND u.year = (select value from zlcw.zl_config where name = 'year')
                                                AND u.season = (select value from zlcw.zl_config where name = 'season')
                                                AND w.is_qs = 1";
                                            $res = Db::query($sql);
                                            if($res[0]["count"]){
                                                $teacher['error'] = 1;
                                                if($errFind) {
                                                    $teacher['desc'] =  $teacher['desc'] . '；【'.$bm.'_'.$zy.'_'.$bj.'】存在已签收的学生';
                                                } else {
                                                    $errCount = $errCount + 1;
                                                    $errFind = true;
                                                    $teacher['desc'] =  '【'.$bm.'_'.$zy.'_'.$bj.'】存在已签收的学生';
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        // 判断辅导员是否存在
                        if (!in_array($teacher['truename'], $mobileTeacherList)) {
                            $teacher['error'] = 1;
                            if($errFind) {
                                $teacher['desc'] =  $teacher['desc'] . '；' . $teacher['truename'] . '辅导员不存在';
                            } else {
                                $errCount = $errCount + 1;
                                $errFind = true;
                                $teacher['desc'] =  $teacher['truename'] . '辅导员不存在';
                            }
                        }

                        // 判断辅导员和部院是否相符
                        
                        // 手机号检测 /^1[34578]{1}\d{9}$/
                        // if(!preg_match("/^1[3|4|5|6|7|8|9][0-9]\d{8}$/", $teacher['mobile'])) {
                        //     $teacher['error'] = 1;
                        //     if(strpos($teacher['desc'], '手机') === false) { 
                        //         if($errFind) {
                        //             $teacher['desc'] =  $teacher['desc'] . '；' . '请输入一个合法的手机号码';
                        //         } else {
                        //             $errCount = $errCount + 1;
                        //             $errFind = true;
                        //             $teacher['desc'] =  $teacher['desc'] . '请输入一个合法的手机号码';
                        //         }
                        //     }
                        // }

                        // 判断辅导员和手机是否相符
                        if (!array_key_exists(trim($teacher['mobile']), $mobileTeacherList)) {
                            $teacher['error'] = 1;
                            if($errFind) {
                                $teacher['desc'] = $teacher['desc'] . '；' . '辅导员手机号不存在'; 
                            } else {
                                $errCount = $errCount + 1;
                                $errFind = true;
                                $teacher['desc'] = $teacher['desc'] . '辅导员手机号不存在'; 
                            }
                        } else {
                            if($teacher['truename'] != $mobileTeacherList[$teacher['mobile']]){
                                $teacher['error'] = 1; 
                                if($errFind) {
                                    $teacher['desc'] =  $teacher['desc'] . '；' . '辅导员手机号与辅导员姓名不符';
                                } else {
                                    $errCount = $errCount + 1;
                                    $errFind = true;
                                    $teacher['desc'] =  $teacher['desc'] . '辅导员手机号与辅导员姓名不符';
                                }
                            }
                        }

                        if ($teacher['error'] == 1) {
                            $errorData[$errCount - 1] = $teacher;
                        }
    
                        $data[$key] = $teacher;
                        unset($teacher);
                    }
                } 
            } else {
                $re['msg']  = $resXls['msg'];
                $re['code']  = -1;
                return $re;
            }
        } catch (Exception $e) {
            $re['msg']  = $e->getMessage();
            $re['code']  = -1;
            return $re;
        }

        $re['count'] = $count;
        $re['errCount'] = $errCount;
        $re['data'] = json_encode($data);
        $re['fileId'] = $fileId;
        $re['filePath'] = $filePath;
        $re['errData'] = json_encode($errorData);
        return $re;
    }

    //执行导入任务
    public function exec(Job $job, $params)
    { 
        $ietaskModle = new Ietask();

        // $ietaskInfo = $ietaskModle->where(['id' => $params['task_id']])->find();

        $teacherInfoModel = new TeacherInfoModel();
        $message = [];
        $count = 0;         // 记录总数
        $progress = 0;
        try {
            $file = json_decode($params['params'], true);
            $resXls=import_excel($file['file_path']); 
            $count = count($resXls['data'])-2;

            if ($resXls['status']) {
                // 清理导入提示
                unset($resXls['data'][0]);
                // 取表头
                $header = $resXls['data'][1];
                unset($resXls['data'][1]);
                $title = $teacherInfoModel->csvHeader(); 
                 
                $fields = []; 
                foreach ($title as $key => $val) {
                    $index = array_search($val['desc'], $header);
                    if ($index >= 0) {
                        $fields[] = [
                            'index' => $index,
                            'value' => $val['id']
                        ];
                    }
                }

                // 提取excel中数据信息
                $iData = [];
                if ($fields) {
                    $i = 0;
                    foreach ($resXls['data'] as $key => $val) {
                        foreach ($fields as $fkey => $fval) {
                            $iData[$i][$fval['value']] = $val[$fval['index']];
                            if(is_object($iData[$i][$fval['value']])) {
                                $iData[$i][$fval['value']] = $iData[$i][$fval['value']]->__toString();
                            }
                        }
                        $i++;
                    }
                }

                // 修改学生信息中的辅导员
                foreach ($iData as $key => $val) { 
                    $bjmc = trim($val['bj']);
                    $zymc = trim($val['zy']);
                    $bmmc = trim($val['bm']);
                    $truename = trim($val['truename']);
                    $mobile = trim($val['mobile']);

                    // 这里直接调用SQL
                    $sql = "UPDATE zl_student_info z
                            INNER JOIN (
                                SELECT a.xh
                                FROM zlcw.zl_student_info a
                                    JOIN zlzhsf.pxsdm c
                                    JOIN zlzhsf.pbjdm d
                                    JOIN zlzhsf.pzydm e
                                    JOIN zlzhsf.pbmdm f
                                WHERE a.xh = c.XH AND c.BJDM = d.BJDM AND c.ZYDM = e.ZYDM AND c.BMDM = f.BMDM
                                    AND d.BJMC = '".$bjmc."'
                                    AND e.ZYMC = '".$zymc."'
                                    AND f.BMMC = '".$bmmc."'
                            ) t ON z.xh = t.xh
                            SET z.teacherId = (SELECT id FROM zl_user 
                            WHERE mobile = '".$mobile."' AND truename = '".$truename."')";
                    $res = Db::query($sql);
                    // Log::info($res);
                    // if($res[0]["count"]){
                        // Log::record("【".$bmmc."_".$zymc."_".$bjmc."】更新辅导员失败！");
                    // }
                }
 
                $uData['status'] = $ietaskModle::IMPORT_SUCCESS_STATUS;
                $uData['message'] = '导入成功';
                if ($message) {
                    $uData['message'] .= json_encode($message);
                }

                $uData['count'] = $count;
                $ietaskModle->update($uData, ['id' => $params['task_id']]);
            } else {
                $uData['status'] = $ietaskModle::IMPORT_FAIL_STATUS;
                $uData['message'] = $resXls['msg'];
                $uData['utime'] = time();
                $ietaskModle->update($uData, ['id' => $params['task_id']]);
            }
        } catch (Exception $e) {
            $message[] = $e->getMessage();
        }
        if ($job->attempts() > 3) {
            $uData['status'] = $ietaskModle::IMPORT_FAIL_STATUS;
            $uData['message'] = '导入执行失败';
            $uData['utime'] = time();
            $ietaskModle->update($uData, ['id' => $params['task_id']]);
            $job->delete();
        }
    }

    public function failed($data)
    {

        // ...任务达到最大重试次数后，失败了
    }

    // 键值对是否存在于数组中
    public function bjbmkeyvalisinlist($key, $val, $li){
        if(!count($li)){
            return false;
        }
		foreach ($li as $k => $v) {
			if($key == $v['bjmc'] && $val == $v['bmmc']){
                return true;
			}
        }
        return false;
    }
	
    // 键值对是否存在于数组中
    public function bjzykeyvalisinlist($key, $val, $li){
        if(!count($li)){
            return false;
        }
		foreach ($li as $k => $v) {
			if($key == $v['bjmc'] && $val == $v['zymc']){
                return true;
			}
        }
        return false;
    }
}
